﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using ExpenseTracker.Entity;

namespace ExpenseTracker.DataAccess
{
    public class CategoryDb : BaseDb
    {
        public CategoryDb(ExpenseTracker.Entity.Account account) : base(account) { }

        public void CreateTable()
        {
            using (SQLiteTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                        DROP TABLE IF EXISTS Category;
                        CREATE TABLE Category
                        (
                           CategoryID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                           Title VARCHAR(50) NOT NULL,
                           TransactionType INTEGER NOT NULL DEFAULT '1' ,
                           InsertDateTime DATETIME NOT NULL,
                           UpdateDateTime DATETIME NOT NULL
                        );
                    ";
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void InsertCategory(ExpenseTracker.Entity.Category category)
        {
            using (DbTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    SQLiteParameter[] parameters = new SQLiteParameter[2];
                    parameters[0] = new SQLiteParameter(DbType.String, 50);
                    parameters[0].Value = category.Title;
                    parameters[1] = new SQLiteParameter(DbType.Int32);
                    parameters[1].Value = (int)category.TransactionType;

                    command.CommandText = @"
                        INSERT INTO [Category] (Title, TransactionType, InsertDateTime, UpdateDateTime) 
                        VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                    ";
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        command.Parameters.Add(parameters[i]);
                    }
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void UpdateCategory(ExpenseTracker.Entity.Category category)
        {
            using (DbTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    SQLiteParameter[] parameters = new SQLiteParameter[2];
                    parameters[0] = new SQLiteParameter(DbType.String, 50);
                    parameters[0].Value = category.Title;
                    parameters[1] = new SQLiteParameter(DbType.Int32);
                    parameters[1].Value = category.CategoryID;

                    command.CommandText = @"
                        UPDATE [Category] 
                        SET 
                            Title = ?, 
                            UpdateDateTime = CURRENT_TIMESTAMP 
                        WHERE CategoryID = ?
                    ";
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        command.Parameters.Add(parameters[i]);
                    }
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public List<Category> GetCategories(TransactionType transactionType)
        {
            List<Category> list = new List<Category>();

            using (SQLiteCommand command = Connection.CreateCommand())
            {
                SQLiteParameter parameter = new SQLiteParameter(DbType.Int32);
                parameter.Value = (int)transactionType;

                command.CommandText = @"
                    SELECT * 
                    FROM [Category] 
                    WHERE TransactionType = ? 
                    ORDER BY [Title]
                ";
                command.Parameters.Add(parameter);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(DataReaderToObject(reader));
                    }
                }                
            }

            return list;
        }

        public Category GetCategory(int categoryID)
        {
            Category item = null;

            using (SQLiteCommand command = Connection.CreateCommand())
            {
                SQLiteParameter parameter = new SQLiteParameter(DbType.Int32);
                parameter.Value = categoryID;

                command.CommandText = @"
                    SELECT * 
                    FROM [Category] 
                    WHERE CategoryID = ?
                ";
                command.Parameters.Add(parameter);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        item = DataReaderToObject(reader);
                        break;
                    }
                }
            }

            return item;
        }

        private Category DataReaderToObject(SQLiteDataReader reader)
        {
            Category item = new Category();
            item.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
            item.Title = reader.GetString(reader.GetOrdinal("Title"));
            item.TransactionType = (TransactionType)reader.GetInt32(reader.GetOrdinal("TransactionType"));
            item.InsertDateTime = reader.GetDateTime(reader.GetOrdinal("InsertDateTime"));
            item.UpdateDateTime = reader.GetDateTime(reader.GetOrdinal("UpdateDateTime"));
            return item;
        }
    }
}
